package net.tycmc.cty.ctyms.timejob.iems.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.util.DateUtil;
import net.tycmc.bulb.common.util.MapGetter;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSDAO;
import net.tycmc.cty.ctyms.timejob.common.StringUtils;

/**
 * 邮件日报
 * 
 * @author lixiaofan
 * 
 */
public class DailyMaillConn {
	IBaseDAO baseDAO;

	public IBaseDAO getBaseDAO() {
		return baseDAO;
	}

	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}

	/**
	 * 获取提示设定中邮件日报定制不为空人员信息集合
	 * 
	 * @return
	 */
	// 3.6.6新增邮件--进出围栏提示，邮件--时效预警，wangkai-20141018
	public List<Map<String, Object>> getUserList() {
		String sql = "SELECT     u2.userid,  username,  u2.email,  smsas_mailflt,  smsas_mailmtn,  smsas_mailalm,  smsas_mailtempalarm,  smsas_mailtemprels, "
				+ "smsas_mailsmokealarm,  smsas_mailsmokerels,  smsas_mailbehavior,  smsas_mailload,  smsas_mailrels,  smsas_mailservice, "
				+ "smsas_mailmkbj,SMSAS_MailFenTip,SMSAS_MailEffAlarm,"
				//4.19新增液位传感器报警
				+ "SMSAS_MailTankLevel,"
				+ "  (SELECT     organizationcode + ',' "
				+ " FROM v_userinfo_organization uo  INNER JOIN "
				+ " organization_db db ON uo.organizationid = db.organizationid "
				+ "WHERE  uo.userid =  u2.userid FOR xml path('')) AS organizationcode , "
				+ "DATEADD(D,-1,GETDATE()) as PromptTime "
				+ "FROM     sms_alarm_setinfo INNER JOIN "
				+ "userinfo u2 ON u2.userid = smsas_usp_id AND u2.usefullife > Getdate() "
				+ "inner join (select  userid  from userinfoaccount u inner join v_userinfo_organization uu on u.UserOrganizationID = uu.UserOrganizationID where UsefulLife > getdate()  group by userid) tb on tb.userid = u2.UserID "
				+ "WHERE      u2.email IS NOT NULL AND u2.email != '' AND (smsas_mailflt = 1 OR "
				+ "smsas_mailmtn = 1 OR "
				+ "smsas_mailalm = 1 OR "
				+ "smsas_mailbehavior = 1 OR "
				+ "smsas_mailload = 1 OR "
				+ "smsas_mailrels = 1 OR "
				+ "smsas_mailservice = 1 OR "
				+ "smsas_mailmkbj = 1 OR "
				+ "smsas_mailtempalarm = 1 OR "
				+ "smsas_mailtemprels = 1 OR "
				+ "smsas_mailsmokealarm = 1 OR "
				+ "smsas_mailsmokerels = 1 OR "
				+ "SMSAS_MailFenTip = 1 OR "
				+ "SMSAS_MailEffAlarm = 1 OR SMSAS_MailTankLevel=1)";
		return baseDAO.execQuery(sql, new Object[] {});
	}

	/**
	 * 获取服务期信息
	 * 
	 * @param dbCode
	 * @return
	 */
	public List<Map<String, Object>> getServiceList(String dbCode) {
		String dbCodeSql = StringUtils.getDbCode(dbCode, "DBCode");
		String sql = "select VehicleID,VehicleNumber,ISNULL(ServiceDeadline,12) as ServiceDeadline, "
				+ "CONVERT(varchar(10),TmnlI_InstallTime,121) as TmnlI_InstallTime, "
				+ "CONVERT(varchar(10),DATEADD(M,ISNULL(ServiceDeadline,12),TmnlI_InstallTime),121) as ServiceExp "
				+ "from V_VehicleInfo "
				+ "where GETDATE()<UsefulLife and Tmnl_SoftEdition is not null and ltrim(rtrim(Tmnl_SoftEdition))!='' "
				+ "and DATEADD(M,ISNULL(ServiceDeadline,12),TmnlI_InstallTime)<DATEADD(d,-1,GETDATE()) "
				+ "and " + dbCodeSql;

		return baseDAO.execQuery(sql, new Object[] {});
	}

	/**
	 * 获取所有提示信息
	 * 
	 * @param dbCode
	 * @return
	 */
	public List<Map<String, Object>> getPromptList(String dbCode) {
		String DbcodeSql = StringUtils.getDbCode(dbCode, "DBCode");
		String FencodeSql = StringUtils.getDbCode(dbCode, "FenO_OrgCode");
		String tableTime = DateUtil.addDay(-1);
		String sql = "select tmp.*,VehicleNumber,operLineName,vehicletypename, "+
				"isnull(ESNNumber,'') ESNNumber,isnull(VehicleContact,'') VehicleContact,isnull(VehicleChargedMobile,'') VehicleChargedMobile from ( "+
//				----故障
				"select MsgFA_Vcl_ID as MsgP_VclID, "+
				"'1' as MsgP_Type, "+
				"MsgP_State=case MsgFA_FaultType when 0 then '1' when 1 then '3' end, "+
				"MsgP_Lo=MsgFA_Longitude, "+
				"MsgP_La=MsgFA_Latitude, "+
				" msgp_detailmsg =SysISM_CodeColorInfo+'<br/>'+SysISM_Code+'<br/>'"+//拼接故障详细内容
                "+case when MsgFA_FaultMLFCtionLamp='0' then '0' when MsgFA_FaultMLFCtionLamp='1' then '1' else '2' end"+
                "+case when MsgFA_FaultRedStopLamp='0' then '0'  when MsgFA_FaultRedStopLamp='1' then'1' else '2' end"+
                "+case when MsgFA_FaultAmberWarnLamp='0'  then '0'  when MsgFA_FaultAmberWarnLamp='1' then '1' else '2' end"+
				"+case when MsgFA_FaultProtectLamp='0'  then '0'  when MsgFA_FaultProtectLamp='1' then '1' else '2' end"+
				"+'<br/>'+SysISM_ContentCN+'<br/>'+CONVERT(varchar(10),MsgFA_FaultCount), "+
				"MsgP_MsgTime=CONVERT(varchar(19),MsgFA_FaultTime,121), "+
				"Msgp_SecTypeID=NULL, "+
				"MsgP_SensorValue=NULL, "+
				"MsgP_ReleaseTime=case MsgFA_FaultType when 1 then CONVERT(varchar(19),MsgFA_FaultTime,121) else NULL end, "+
				"FenceName=NULL "+
				"from  "+GlobalDBVar.getDBFullName_Conn("Msg_FaultAlarm_"+tableTime.replaceAll("-", ""), "4", tableTime)+" inner join "+GlobalDBVar.getDBFullName_Conn("Sys_FltISM", "1", null)+" on MsgFA_SPN=SysISM_SPN and MsgFA_FMI=SysISM_FMI "+
				" union "+
//				---机械报警  和  传感器报警
				"select MsgAV_Vcl_ID as MsgP_VclID, "+
				"MsgP_Type=case  when MsgAV_SysA_ID=17 or MsgAV_SysA_ID=18 then '14' else '2' end, "+
				"MsgP_State=NULL, "+
				"MsgAV_Longitude, "+
				"MsgAV_Latitude, "+
				"MsgP_DetailMsg=SysA_ItemCN, "+
				"MsgP_MsgTime=CONVERT(varchar(19),MsgAV_Happen,121) , "+
				"Msgp_SecTypeID=MsgAV_SysA_ID, "+
				"MsgP_SensorValue=case  when MsgAV_SysA_ID=17 then '温度异常' when MsgAV_SysA_ID=18 then '烟度异常' end, "+
				"MsgP_ReleaseTime=NULL, "+
				"FenceName=NULL "+
				"from "+GlobalDBVar.getDBFullName_Conn("Msg_Alarm_Vcl_"+tableTime.replaceAll("-", ""), "4", tableTime)+" "+
				"inner join "+GlobalDBVar.getDBFullName_Conn("Sys_Alarm", "3", null)+" on SysA_ID=MsgAV_SysA_ID "+" "+
				"union "+
//				----门控报警
				"select MsgADC_Vcl_ID as MsgP_VclID, "+
				"'9' as MsgP_Type, "+
				"MsgP_State=NULL, "+
				"MsgP_Lo=MsgADC_Longitude, "+
				"MsgP_La=MsgADC_Latitude, "+
				"MsgP_DetailMsg=NULL, "+
				"MsgP_MsgTime=CONVERT(varchar(19),MsgADC_AlarmTime,121), "+
				"Msgp_SecTypeID=MsgADC_AlarmType, "+
				"MsgP_SensorValue=NULL, "+
				"MsgP_ReleaseTime=NULL, "+
				"FenceName=NULL "+
				"from "+GlobalDBVar.getDBFullName_Conn("Msg_Alarm_DoorControl_"+tableTime.replaceAll("-", ""), "4", tableTime)+" "+
				"union "+
//				---进出围栏报警
				"select MsgAF_Vcl_ID as MsgP_VclID, "+
				"'15' as MsgP_Type, "+
				"MsgP_State=NULL, "+
				"MsgP_Lo=MsgAF_Longitude, "+
				"MsgP_La=MsgAF_Latitude, "+
				"MsgP_DetailMsg=NULL, "+
				"MsgP_MsgTime=CONVERT(varchar(19),MsgAF_AlarmTime,121), "+
				"Msgp_SecTypeID=MsgAF_AlarmType, "+
				"MsgP_SensorValue=NULL, "+
				"MsgP_ReleaseTime=NULL, "+
				"FenceName "+
				"from "+GlobalDBVar.getDBFullName_Conn("Msg_Alarm_Fence_"+tableTime.replaceAll("-", ""), "4", tableTime)+" "+
				"inner join organization_fencelist on FenceID=MsgAF_FenceID "+
				"LEFT JOIN (select feno_fenceid from fence_org where "+FencodeSql+" group by FenO_FenceID)fo on fo.feno_fenceid=FenceID  "+
				"union "+
				//液位传感器报警
				"select MsgATL_Vcl_ID as MsgP_VclID, "+
				"'16' as MsgP_Type, "+
				"MsgP_State=NULL, "+
				"MsgP_Lo=MsgATL_Longitude, "+
				"MsgP_La=MsgATL_Latitude, "+
				"MsgP_DetailMsg=NULL, "+
				"MsgP_MsgTime=CONVERT(varchar(19),MsgATL_HappenTime,121), "+
				"Msgp_SecTypeID=NULL, "+
				"MsgP_SensorValue=NULL, "+
				"MsgP_ReleaseTime=NULL, "+
				"FenceName=NULL "+
				"from "+GlobalDBVar.getDBFullName_Conn("Msg_Alarm_TankLevel_"+tableTime.replaceAll("-", ""), "4", tableTime)+" where MsgATL_ChangeState=1"+
				")tmp "+
				"inner join V_VehicleInfo on MsgP_VclID=VehicleID and "+DbcodeSql+ 
				"order by MsgP_MsgTime desc";
		
		MyConnectDSDAO myConnectDSDAO=new MyConnectDSDAO();
		return myConnectDSDAO.execQuery(sql, new Object[] {});
	}

	/**
	 * 获取今天的保养提示信息
	 * 
	 * @param dbCode
	 * @return
	 * @author jiyongtian 20140628 IEMS3.6新增
	 */
	public List<Map<String, Object>> getPromptOfMainAlert(String dbCode) {
		String DbcodeSql = StringUtils.getDbCode(dbCode, "DBCode");
		String sql = "select MsgP_Type,VehicleNumber,MsgP_DetailMsg,CONVERT(varchar(19),MsgP_MsgTime,121) MsgP_MsgTime, "
				+"CONVERT(varchar(19),MsgP_ReleaseTime,121) MsgP_ReleaseTime,Msgp_SecTypeName,Msgp_SecTypeID,MsgP_State, "
				+"cast(round(MsgLA_BgnLoad,2) as decimal(18,2)) MsgLA_BgnLoad, cast(round(MsgLA_AftLoad,2) as decimal(18,2))MsgLA_AftLoad,MsgLA_Lng,MsgLA_Lat, "
				//增加设备型号、保养列
				+"vehicletypename,MtnAL_AlarmDate = CONVERT(varchar(19),MtnAL_AlarmDate,120),MtnAL_AlarmValue = STR(MtnAL_AlarmValue) + case (MtnAL_AlarmType) when 1 then '小时' when 2 then '公里' when 3 then '天' end, "
				+"MtnAL_AlarmType,MtnAL_AlarmTypeName = case (MtnAL_AlarmType) when 1 then '工作小时数' when 2 then '公里数' when 3 then '固定时间间隔' end "
				+"from Msg_Prompt "
				+"inner join V_VehicleInfo on MsgP_VclID=VehicleID and  "+DbcodeSql
				+"left join Mtn_Alarm_InfoList mai on mai.MtnAL_VclID = MsgP_VclID and msgp_sectypeid = mtnal_alarmtype "
				+"left join Msg_LoadAwoke on MsgP_MsgID=MsgLA_ID and MsgP_Type=5 "
				+"where MtnAL_AlarmDate = '"+DateUtil.addDay(0)+"' "
				+"and MsgP_Type = 3 "
				+"order by MsgP_MsgTime desc";
		return baseDAO.execQuery(sql, new Object[]{});
	}

	/*
	 * 3.6.6新增获取提示开关表中提示开关为关的设备集合 wangkai-20141020
	 */
	public Map<String, List<String>> getPromptSwitch() {

		Map<String, List<String>> hm = new HashMap<String, List<String>>();

		String sql = "select VclPS_Type,VclPS_VclID from " +GlobalDBVar.getDBFullName_Conn("VclPromptSwitch","1",null)+
				" where VclPS_isOpen=0";
		List<Map<String, Object>> list = baseDAO
				.execQuery(sql, new Object[] {});
		for (int i = 1; i <= 7; i++) {
			List<String> PromSwitchList = new ArrayList<String>();
			for (Map<String, Object> map : list) {
				if (MapGetter.getInt(map, "VclPS_Type") == i) {
					PromSwitchList.add(MapGetter.getString(map, "VclPS_VclID"));
				}
			}
			hm.put(i + "", PromSwitchList);
		}

		return hm;
	}

	public int UpdatePosition(String sql) {
		return this.baseDAO.execUpdate(sql, new Object[] {});
	}

}